package pers.cz.easyexcel.tools;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import pers.cz.easyexcel.style.DefaultCellStyle;
import utils.DateUtils;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @program: PostGirl-panent
 * @description: PoiOoxmlUtils
 * @author: Cheng Zhi
 * @create: 2022-04-15 14:57
 **/
public class PoiUtils {

    /**
     * 获取workbook
     * @param fileInputStream
     * @param fileName
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream fileInputStream, String fileName) throws IOException {

        // 1、创建一个workbook对象
        Workbook workbook = null;
        if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(fileInputStream);
        } else if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else {
            throw new RuntimeException("请输入正确的文件格式");
        }

        return workbook;
    }

    /**
     * 获取单元值
     * @param sheet
     * @param row
     * @param column  列
     * @param rowNum  行
     * @return
     */
    private String getCellValue(Sheet sheet, XSSFRow row, Integer column, Integer rowNum) {
        String value = "";
        XSSFCell cell = row.getCell(column);
        if (cell == null || cell.equals("")) {
            return value;
        }
        // 获取单元格值，如果不是合并单元格则返回null
        String mergedRegionValue = getMergedRegion(sheet, rowNum, column);
        if (mergedRegionValue != null) {
            value = mergedRegionValue;
        } else {
            value = getCellValue(cell);
        }

        return  value;
    }
    /**
     * 标题行校验
     * @param rowNum
     * @param sheet
     * @return true：需要解析  false：不必解析
     */
    private boolean checkTitleRow(int rowNum, Sheet sheet, Map<String,Integer> map, List<String> list) {
        boolean flag = true;
        if (rowNum == 0) {

            flag = false;
            // 标题行筛选
            XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
            if (row == null) {
                return false;       // 标题行为空则不解析
            }

            short lastCellNum = row.getLastCellNum();

            for (int s = 0; s<lastCellNum; s++) {
                XSSFCell cell = row.getCell(s);
                if (cell == null || cell.equals("")) {
                    continue;
                }
                String cellValue = getCellValue(cell);
                for (String keywords : list) {
                    if (cellValue != null && cellValue.contains(keywords)) {
                        map.put(cellValue,s);
                        flag = true;
                    }
                }

            }
        }
        return flag;
    }
    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 判断是否为合并单元格
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {

        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i=0; i<numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int lastRow = mergedRegion.getLastRow();
            //System.out.println(firstRow + "--" + firstColumn + "--" + lastColumn + "--" +  lastRow);

            if ((row >= firstRow && row <= lastRow) && (column >= firstColumn && column <= lastColumn) ) {
                Row row1 = sheet.getRow(firstRow);
                Cell cell = row1.getCell(firstColumn);
                return true;
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegion(Sheet sheet, int row, int column) {
        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i=0; i<numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int lastRow = mergedRegion.getLastRow();
            //System.out.println(firstRow + "--" + firstColumn + "--" + lastColumn + "--" +  lastRow);

            if ((row >= firstRow && row <= lastRow) && (column >= firstColumn && column <= lastColumn) ) {
                Row row1 = sheet.getRow(firstRow);
                Cell cell = row1.getCell(firstColumn);
                return getCellValue(cell);
            }
        }
        return null;
    }
    /**
     * 获取单元格值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {

        if (cell == null) {
            return null;
        }

        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                return DateUtils.format(date, "yyyy-MM-dd HH:mm:ss");
            }
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.FORMULA) {
            return cell.getCellFormula();
        }
/*
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        }*/
        return null;
    }

    public static void writeCell(Cell cell, Object value) {

        if (value == null) {
            return;
        }
        if (value instanceof Integer || value instanceof Long || value instanceof Float || value instanceof String) {
            cell.setCellValue(String.valueOf(value));
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(DateUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss"));
            // cell.setCellValue((Date) value);
        } else {
            throw new IllegalArgumentException("暂时不支持的类型:" + value.getClass().getName());
        }
    }
}
